-- @owner: opentestcase007
-- @date: 2022-02-14
-- @testpoint: 自定义函数同义词在触发器中调用

--step1: 创建源表及触发表; expect: 创建源表及触发表成功
drop table if exists t_ustore_synonym_0023_01 cascade;
drop table if exists t_ustore_synonym_0023_02 cascade;
create table t_ustore_synonym_0023_01
(
	id int,
    name varchar2(10),
	sal number
) with (storage_type=ustore);;
create table t_ustore_synonym_0023_02
(
	id int,
    name varchar2(10),
	sal number
) with (storage_type=ustore);;

--step2: 创建自定义函数; expect: 创建自定义函数成功
drop function if exists func_ustore_synonym_0023(c bigint);
create or replace function func_ustore_synonym_0023(c bigint) return int
as
  b int := c;
begin
	for i in 1..c loop
		b := b + 1;
	end loop;
	return b;
end;
/

--step3: 创建自定义函数同义词; expect: 创建自定义函数同义词成功
drop synonym if exists syn_func_ustore_synonym_0023;
create or replace synonym syn_func_ustore_synonym_0023 for func_ustore_synonym_0023;

--step4: 创建触发器函数; expect: 创建触发器函数成功
create or replace function func_tri_ustore_synonym_0023() returns trigger as
$$
declare
begin
       insert into t_ustore_synonym_0023_02 values(syn_func_ustore_synonym_0023(syn_func_ustore_synonym_0023(3)),new.name,new.sal);
       return new;
end
$$ language plpgsql;
/

--step5: 创建触发器; expect: 创建触发器成功
create trigger tri_ustore_synonym_0023
before insert on t_ustore_synonym_0023_01
for each row
execute procedure func_tri_ustore_synonym_0023();
/

--step6: 源表中插入数据; expect: 源表中插入数据成功
insert into t_ustore_synonym_0023_01 values(1,'aaa',2600);
insert into t_ustore_synonym_0023_01 values(2,'bbb',2600);
insert into t_ustore_synonym_0023_01 values(3,'ccc',2800);

--step7: 调用触发器; expect: 调用触发器成功
select name,sal from t_ustore_synonym_0023_01;
select name,sal from t_ustore_synonym_0023_02;

--step8: 清理环境; expect: 清理环境成功
drop table if exists t_ustore_synonym_0023_01 cascade;
drop table if exists t_ustore_synonym_0023_02 cascade;
drop function if exists func_ustore_synonym_0023(c bigint) cascade;
drop function func_tri_ustore_synonym_0023();
drop synonym if exists syn_func_ustore_synonym_0023;
